/******************************************************************************
 * @File name   :      MemberPackageDaoImpl.java
 *
 * @Author      :      JIZHANGS
 *
 * @Date        :      Jul 24, 2015
 *
 * @Copyright Notice: 
 * Copyright (c) 2015 Capgemini, Inc. All  Rights Reserved.
 * This software is published under the terms of the Capgemini Software
 * License version 1.0, a copy of which has been included with this
 * distribution in the LICENSE.txt file.
 * 
 * 
 * ----------------------------------------------------------------------------
 * Date                   Who         Version        Comments
 * Jul 24, 2015 12:34:47 PM        JIZHANGS     1.0            Initial Version
 *****************************************************************************/
package com.cap.bts.crmdp.dealer.purchase.dao.impl;

import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.TimeZone;

import javax.inject.Inject;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import com.cap.bts.commonfunc.common.util.UserUtil;
import com.cap.bts.crmdp.dealer.activation.dto.ActivationQueryDto;
import com.cap.bts.crmdp.dealer.card.constants.CardConstants;
import com.cap.bts.crmdp.dealer.card.dao.ICardCreateDao;
import com.cap.bts.crmdp.dealer.card.domain.CardDefinition;
import com.cap.bts.crmdp.dealer.purchase.constants.PurchaseConstants;
import com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao;
import com.cap.bts.crmdp.dealer.purchase.dao.IPackageDefinitionDao;
import com.cap.bts.crmdp.dealer.purchase.domain.MemberPackageInfo;
import com.cap.bts.crmdp.dealer.purchase.domain.PackageDefinition;
import com.cap.bts.crmdp.dealer.redemption.dto.MemberAndDealerInfoDto;
import com.cap.bts.crmdp.dealer.redemption.dto.MemberQueryDto;
import com.cap.bts.framework.common.dao.callback.NativeQueryBySqlJpaCallback;
import com.cap.bts.framework.common.dao.callback.NativeQueryJpaCallback;
import com.cap.bts.framework.common.dao.impl.BaseDaoImpl;
import com.cap.bts.framework.common.exception.AppException;
import com.cap.bts.framework.common.util.Utils;

/**
 *
 */
@Repository("memberPackageDao")
public class MemberPackageDaoImpl extends BaseDaoImpl<MemberPackageInfo, Long> implements
    IMemberPackageDao {
    private static final Logger LOGGER = Logger.getLogger(MemberPackageDaoImpl.class);
    @Autowired
    private IPackageDefinitionDao packageDefinitionDao;

    @Autowired
    private ICardCreateDao cardCreateDao;

    /**
     * @Date : Jul 24, 2015
     * @param clazz
     */
    @Inject
    public MemberPackageDaoImpl(
        @Value("com.cap.bts.crmdp.dealer.purchase.domain.MemberPackageInfo") Class<MemberPackageInfo> clazz) {
        super(clazz);
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Jul 24, 2015
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#addMemberPackageInfo(com.cap.bts.crmdp.dealer.purchase.domain.MemberPackageInfo)
     **/
    @Override
    public MemberPackageInfo addMemberPackageInfo(MemberPackageInfo mpi) throws AppException {
        PackageDefinition pd = packageDefinitionDao.findById(mpi.getPackageDefinition().getId());
        CardDefinition cd = null;
        MemberPackageInfo maxMpi = null;
        if (pd != null) {
            cd = pd.getCardDefinition();
            maxMpi = this.getMaxMemberPackageInfoByNonPhysicalCardPrefix(cd.getPerfix());
        }
        String cardnumMax="";
        String cardnum="0000000000";
        //是否卡已经用光时卡号判断
        if(maxMpi!=null){
            cardnumMax = CardConstants.getMaxNum(maxMpi.getCardDefinition().getCardNum(), maxMpi.getCardDefinition().getPerfix(),maxMpi.getCardDefinition().getLengthOfDigital());
            cardnum=maxMpi.getPackageNumber();
        }
        
        if(mpi.getCardDefinition().getCheckBitEnabeld()==1){
            cardnum=cardnum.substring(0,cardnum.length()-1);   
        }
        
        //如果是最大卡号则显示为已用完
        if(cardnumMax!=null && cardnumMax.equals(cardnum)){
            throw new AppException("purchasePackage", "该类型卡号已用完,请联系管理员！");
        }
        
        if (maxMpi != null) {
            cd.setCardNum(maxMpi.getPackageNumber());
        }else{
        	cd.setCardNum("0000000000");
        }
        
        String numString = cardCreateDao.getNextCardNumFrom(cd);
        if (numString != null) {
            // 调用function卡号生成
            mpi.setPackageNumber(numString);
            // 反写新的卡号
            cd.setCardNum(numString);
        }
        mpi.setMarsLockStatus(0);
        MemberPackageInfo rtnMemberPackageInfo=null;
        try {
            rtnMemberPackageInfo=super.save(mpi);
        } catch (Exception e) {
            LOGGER.error("addMemberPackageInfo", e);
            throw new AppException("purchasePackage", "虚拟卡最新卡号存在更新,请重新操作！");
        }
        return rtnMemberPackageInfo;
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Jul 24, 2015
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#updateMemberPackageInfo(com.cap.bts.crmdp.dealer.purchase.domain.MemberPackageInfo)
     **/
    @SuppressWarnings("unchecked")
    @Override
    public void updateMemberPackageInfo(MemberPackageInfo memberPackageInfo) throws AppException {
        List<MemberPackageInfo> list = (List<MemberPackageInfo>) super.getJpaTemplate().find(
            "from MemberPackageInfo a where a.packageNumber= '"
                + memberPackageInfo.getPackageNumber() + "'");
        if (list != null && !list.isEmpty()) {
            MemberPackageInfo mpi = super.findById(list.get(0).getId());
            mpi.setPurchaseDate(memberPackageInfo.getPurchaseDate());
            mpi.setStatus(memberPackageInfo.getStatus());
            mpi.setCarId(memberPackageInfo.getCarId());
            mpi.setPackageDefinition(memberPackageInfo.getPackageDefinition());
            mpi.setDealerId(memberPackageInfo.getDealerId());
            mpi.setMemberId(memberPackageInfo.getMemberId());
            mpi.setStatus(memberPackageInfo.getStatus());
            mpi.setActualPrice(memberPackageInfo.getActualPrice());
            mpi.setMarsLockStatus(memberPackageInfo.getMarsLockStatus());
            mpi.setSoNumberSale(memberPackageInfo.getSoNumberSale());
            super.save(mpi);
        }
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Jul 24, 2015
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#updateMemberPackageInfo(com.cap.bts.crmdp.dealer.purchase.domain.MemberPackageInfo)
     **/
    @SuppressWarnings("unchecked")
    @Override
    public void updateMemberPackageInfoById(MemberPackageInfo memberPackageInfo)
        throws AppException {
        List<MemberPackageInfo> list = (List<MemberPackageInfo>) super.getJpaTemplate().find(
            "from MemberPackageInfo a where a.id=" + memberPackageInfo.getId());
        if (list != null && !list.isEmpty()) {
            list.get(0).setStatus((byte) 3);
            super.update(list.get(0));
        }
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Jul 28, 2015
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#getMemberPackageInfo(com.cap.bts.crmdp.dealer.purchase.domain.MemberPackageInfo)
     **/
    @Override
    public MemberPackageInfo getUnPurchaseMemberPackageInfo(MemberPackageInfo memberPackageInfo)
        throws AppException {
        MemberPackageInfo mpi = null;
        List<MemberPackageInfo> list = super
            .getListByQuery("from MemberPackageInfo a where a.packageNumber='"
                + memberPackageInfo.getPackageNumber() + "' and a.status<1 and a.marsLockStatus=0");
        if (list != null && !list.isEmpty()) {
            mpi = list.get(0);
        } else {
            mpi = null;
        }
        return mpi;
    }
    
    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Jul 28, 2015
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#getMemberPackageInfo(com.cap.bts.crmdp.dealer.purchase.domain.MemberPackageInfo)
     **/
    @Override
    public MemberPackageInfo getPurchasedMemberPackageInfo(MemberPackageInfo memberPackageInfo)
        throws AppException {
        MemberPackageInfo mpi = null;
        List<MemberPackageInfo> list = super
            .getListByQuery("from MemberPackageInfo a where a.packageNumber='"
                + memberPackageInfo.getPackageNumber() + "' and a.status=1 and a.marsLockStatus=0");
        if (list != null && !list.isEmpty()) {
            mpi = list.get(0);
        } else {
            mpi = null;
        }
        return mpi;
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#
     * getMemberPackageInfoByActivationDto
     * (com.cap.bts.crmdp.dealer.activation.dto.ActivationQueryDto)
     */
    @Override
    public MemberPackageInfo getMemberPackageInfoByActivationDto(ActivationQueryDto dto)
        throws AppException {
        StringBuilder hql = new StringBuilder("from MemberPackageInfo a where 1=1 ");

        if (!Utils.isNullOrEmpty(dto.getPackageNumber())) {
            hql.append(" and a.packageNumber='" + dto.getPackageNumber() + "'");
        }

        if (!Utils.isNullOrEmpty(dto.getPackageActiveCode())) {
            hql.append(" and a.activationCode='" + dto.getPackageActiveCode() + "'");
        }

        hql.append(" and a.status<2");

        // hql.append(" and a.marsLockStatus!=1")

        List<MemberPackageInfo> list = super.getListByQuery(hql.toString());
        if (list != null && !list.isEmpty()) {
            return list.get(0);
        } else {
            return null;
        }
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Jul 31, 2015
     * @see com.cap.bts.crmdp.dealer.redemption.dao.IMemberPackageDao#getPackageListByMember(com.cap.bts.crmdp.dealer.redemption.dto.MemberQueryDto)
     **/
    @SuppressWarnings("unchecked")
    @Override
    public List<Object[]> getPackageListByMember(MemberQueryDto dto) throws AppException {
        String hql = "select a.id,CONCAT(b.name,if(a.is_Gift=1, '(赠送)','')), a.package_Number, a.status, a.purchase_Date, a.active_Date, a.expire_Date from TM_MEMBER_PACKAGE_INFO a inner join TM_CRM_PACKAGE_DEFINITION b where a.MARS_LOCK_STATUS='0' and a.PACKAGE_DEF_ID=b.ID and a.active_Member_Id="
            + dto.getId()
            + " and a.active_Car_Id='"
            + dto.getCarId()
            + "' order by a.status asc,a.active_Date asc,a.id asc";
        // super.getJpaTemplate().getEntityManager().createNativeQuery(hql).getResultList()
        return getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<Object[]>(hql));
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Aug 1, 2015
     * @see com.cap.bts.crmdp.dealer.redemption.dao.IMemberPackageDao#checkActivePackage(com.cap.bts.crmdp.dealer.redemption.dto.MemberQueryDto)
     **/
    @Override
    public Boolean checkActivePackage(MemberQueryDto dto) throws AppException {

        int count = super
            .countByQuery("select count(*) from MemberPackageInfo a where a.activeMemberId="
                + dto.getId() + "and a.activeCarId=" + dto.getCarId());

        if (count > 0) {
            return true;
        }

        return false;
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Aug 5, 2015
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#getNonPhysicalCardNumber()
     **/
    @SuppressWarnings("unchecked")
    @Override
    public String getNonPhysicalCardNumber(Long packageDefId) throws AppException {
        StringBuilder packageNumber = new StringBuilder();
        List<PackageDefinition> pdList = (List<PackageDefinition>) super.getJpaTemplate().find(
            "from PackageDefinition a where a.id=" + packageDefId);
        if (pdList != null && !pdList.isEmpty()) {
            packageNumber.append(pdList.get(0).getCode());
            packageNumber.append(String.format("%07d", pdList.get(0).getNumberSequence() + 1));
            pdList.get(0).setNumberSequence(pdList.get(0).getNumberSequence() + 1);
            packageDefinitionDao.update(pdList.get(0));
        }
        return packageNumber.toString();
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : 2015年8月19日
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#chackPackageNumberByActivationDto(com.cap.bts.crmdp.dealer.activation.dto.ActivationQueryDto)
     **/
    @Override
    public MemberPackageInfo checkPackageNumberByActivationDto(ActivationQueryDto dto)
        throws AppException {
        StringBuilder hql = new StringBuilder("from MemberPackageInfo a where 1=1 ");
        if (!Utils.isNullOrEmpty(dto.getPackageNumber())) {
            hql.append(" and a.packageNumber='" + dto.getPackageNumber() + "'");
        }
        List<MemberPackageInfo> list = super.getListByQuery(hql.toString());
        if (list != null && !list.isEmpty()) {
            return list.get(0);
        } else {
            return null;
        }
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : 2015年8月19日
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#chackActivationCodeByActivationDto(com.cap.bts.crmdp.dealer.activation.dto.ActivationQueryDto)
     **/
    @Override
    public MemberPackageInfo checkPackageNumberAndActivationCodeByActivationDto(
        ActivationQueryDto dto) throws AppException {
        StringBuilder hql = new StringBuilder("from MemberPackageInfo a where 1=1 ");
        if (!Utils.isNullOrEmpty(dto.getPackageNumber())) {
            hql.append(" and a.packageNumber='" + dto.getPackageNumber() + "'");
        }
        if (!Utils.isNullOrEmpty(dto.getPackageActiveCode())) {
            hql.append(" and a.activationCode='" + dto.getPackageActiveCode() + "'");
        }
        List<MemberPackageInfo> list = super.getListByQuery(hql.toString());
        if (list != null && !list.isEmpty()) {
            return list.get(0);
        } else {
            return null;
        }
    }

    @Override
    public List<MemberPackageInfo> getAllActiveMemberPackageList() throws AppException {
        try {
            String hql = "from MemberPackageInfo m where m.status="
                + PurchaseConstants.PACKAGE_STATUS_ACTIVED;
            return getListByQuery(hql);
        } catch (Exception e) {
            throw new AppException("Exception getAllActiveMemberPackageList", e);
        }
    }
    
    @Override
    public List<MemberPackageInfo> getAllActiveExpireMemberPackageList() throws AppException {
        try {
        	
        	SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        	format.setTimeZone(TimeZone.getTimeZone("GMT+8:00"));
        	
        	
            String hql = "from MemberPackageInfo m where m.status="
                + PurchaseConstants.PACKAGE_STATUS_ACTIVED
//                + " and m.expireDate > str_to_date('"+ format.format(new Date())+"', '%Y-%m-%d')";
            + " and datediff(m.expireDate, str_to_date('"+ format.format(new Date())+"', '%Y-%m-%d'))  <= 0";
            return getListByQuery(hql);
        } catch (Exception e) {
            throw new AppException("Exception getAllActiveMemberPackageList", e);
        }
    }
    

    @Override
    public List<MemberPackageInfo> getMemberPackageList(int status) throws AppException {
        try {
            String hql = "from MemberPackageInfo m where m.status=" + status;
            return getListByQuery(hql);
        } catch (Exception e) {
            throw new AppException("Exception getAllActiveMemberPackageList", e);
        }
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : 2015年9月5日
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#getMemberPackageInfoByActiveDealerIdAndActiveMemberId(java.lang.Long,
     *      java.lang.Long)
     **/
    @Override
    public List<MemberPackageInfo> getMemberPackageInfoByActiveDealerIdAndActiveMemberId(
        Long dealerId, Long memberId) throws AppException {
        try {
            String hql = "from MemberPackageInfo m where m.activeDealerId=" + dealerId
                + " and m.activeMemberId=" + memberId;
            return super.getListByQuery(hql);
        } catch (Exception e) {
            throw new AppException(
                "Exception getMemberPackageInfoByActiveDealerIdAndActiveMemberId", e);
        }
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : 2015年9月6日
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#updateMemberPackageInfoByMemberPackageInfo(com.cap.bts.crmdp.dealer.purchase.domain.MemberPackageInfo)
     **/
    @Override
    public void updateMemberPackageInfoByMemberPackageInfo(MemberPackageInfo memberPackageInfo,
        Long newMemberId) throws AppException {
        String hql = "update MemberPackageInfo m set m.activeMemberId=" + newMemberId + " "
            + "where m.activeDealerId=" + memberPackageInfo.getActiveDealerId()
            + " and m.activeMemberId=" + memberPackageInfo.getActiveMemberId();
        this.updateByQuery(hql);
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Aug 28, 2015
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#getMemberPackageInfoListByBatchId(java.lang.Long)
     **/
    @Override
    public List<MemberPackageInfo> getMemberPackageInfoListByBatchId(Long batchId)
        throws AppException {
        return super
            .getListByQueryWithVaParam(
                "select a from MemberPackageInfo a inner join a.cardGeneration where a.cardGeneration.id=?",
                batchId);
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : 2015年9月17日
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#checkPackage(com.cap.bts.crmdp.dealer.redemption.dto.MemberQueryDto)
     **/
    @Override
    public Boolean checkPackage(MemberQueryDto dto) throws AppException {
        int count = super.countByQuery("select count(*) from MemberPackageInfo a"
            + " where a.memberId=" + dto.getId());
        if (count > 0) {
            return true;
        }
        return false;
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : 2015年9月17日
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#getMemberAndDealerInfoDto(com.cap.bts.crmdp.dealer.redemption.dto.MemberQueryDto)
     **/
    @Override
    public List<MemberAndDealerInfoDto> getDealerInfoDto() throws AppException {
        String hql = "select a.DEALER_NAME, a.ADDRESS" + " from tm_raw_dealer a" + " where a.ID="
            + UserUtil.getCurrentUser().getDealerId();
        List<Object[]> resultList = super.getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(hql));
        List<MemberAndDealerInfoDto> result = new ArrayList<MemberAndDealerInfoDto>();
        for (Object[] i : resultList) {
            MemberAndDealerInfoDto it = new MemberAndDealerInfoDto();
            it.setDealerName(Utils.getString(i[0]));
            it.setAddress(Utils.getString(i[1]));
            result.add(it);
        }
        return result;

    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : 2015年9月18日
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#getPackageListForReportByMember(com.cap.bts.crmdp.dealer.redemption.dto.MemberQueryDto)
     **/
    @Override
    public List<Object[]> getPackageListForReportByMember(MemberQueryDto dto) throws AppException {
        String sql = "select a.ACTUAL_PRICE, b.NAME, a.PACKAGE_NUMBER, a.STATUS, a.PURCHASE_DATE, a.ACTIVE_DATE, a.EXPIRE_DATE, a.ID"
            + " from TM_MEMBER_PACKAGE_INFO a, TM_CRM_PACKAGE_DEFINITION b "
            + " where a.MEMBER_ID="
            + dto.getId()
            + " and a.CAR_ID="
            + dto.getCarId()
            + " and a.STATUS = "
            + PurchaseConstants.PACKAGE_STATUS_SALED
            + " and a.MARS_LOCK_STATUS = '"
            + PurchaseConstants.MARS_NO_LOCK_STATUS + "'"
            + " and a.PACKAGE_DEF_ID = b.ID"
            + " union "
            + " select b.PRICE, b.NAME, a.PACKAGE_NUMBER, a.STATUS, a.PURCHASE_DATE, a.ACTIVE_DATE, a.EXPIRE_DATE, a.ID"
            + " from TM_MEMBER_PACKAGE_INFO a, TM_CRM_PACKAGE_DEFINITION b "
            + " where a.ACTIVE_MEMBER_ID="
            + dto.getId()
            + " and a.ACTIVE_CAR_ID="
            + dto.getCarId()
            + " and a.STATUS != "
            + PurchaseConstants.PACKAGE_STATUS_SALED
            + " and a.MARS_LOCK_STATUS = '"
            + PurchaseConstants.MARS_NO_LOCK_STATUS + "'"
            + " and a.PACKAGE_DEF_ID = b.ID";
        return super.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback(sql));
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Sep 23, 2015
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#getPackagePurchaseNum(java.lang.Long,
     *      java.lang.Long, java.lang.Long)
     **/
    @Override
    public int getPackagePurchaseNum(Long dealerId, Long memberId, Long carId, Long packageDefId)
        throws AppException {
        return super
            .countByQuery("select count(a.id) from MemberPackageInfo a where a.packageDefinition.id="
                + packageDefId
                + " and a.activeDealerId = "
                + dealerId
                + " and a.activeMemberId= "
                + memberId + " and a.activeCarId = " + carId);
        // return
        // super.countByQueryWithVaParam("select count(1) from MemberPackageInfo a where a.activeDealerId = ?1 and a.activeMemberId= ?2 and a.activeCarId = ?3",dealerId,memberId,carId)
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Oct 20, 2015
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#getPackagePurchaseNumWithPackageCode(java.lang.Long,
     *      java.lang.Long, java.lang.Long, java.lang.Long)
     **/
    @Override
    public int getPackagePurchaseNumWithPackageCode(Long dealerId, Long memberId, Long carId,
        Long packageDefId) throws AppException {
        return super
            .countByNativeQuery("select count(1) from TM_MEMBER_PACKAGE_INFO aa ,TM_CRM_PACKAGE_DEFINITION bb where aa.PACKAGE_DEF_ID=bb.id and aa.active_Dealer_Id = "
                + dealerId
                + " and aa.active_Member_Id= "
                + memberId
                + " and aa.active_Car_Id = "
                + carId
                + " and bb.code in  (select b.code from TM_MEMBER_PACKAGE_INFO a ,TM_CRM_PACKAGE_DEFINITION b where a.PACKAGE_DEF_ID=b.id and a.PACKAGE_DEF_ID="
                + packageDefId
                + " and a.active_Dealer_Id = "
                + dealerId
                + " and a.active_Member_Id= " + memberId + " and a.active_Car_Id = " + carId + " )");
        // return
        // super.countByQueryWithVaParam("select count(1) from MemberPackageInfo a where a.activeDealerId = ?1 and a.activeMemberId= ?2 and a.activeCarId = ?3",dealerId,memberId,carId)
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Oct 20, 2015
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#checkPackagePurchaseNumWithPackageCode(java.lang.Long,
     *      java.lang.Long, java.lang.Long, java.lang.Long)
     **/
    @Override
    public int checkPackagePurchaseNumWithPackageCode(Long dealerId, Long memberId, Long carId,
        Long packageDefId) throws AppException {
        return super
            .countByNativeQuery("SELECT count(1) FROM (select * from (select bb.code,bb.number_Sequence from TM_CRM_PACKAGE_DEFINITION bb where bb.code in  (select b.code from TM_MEMBER_PACKAGE_INFO a ,TM_CRM_PACKAGE_DEFINITION b where a.PACKAGE_DEF_ID=b.id and a.PACKAGE_DEF_ID="
                + packageDefId
                + " and a.active_Dealer_Id = "
                + dealerId
                + " and a.active_Member_Id= "
                + memberId
                + " and a.active_Car_Id = "
                + carId
                + "  ) ) t  group by t.code,t.number_Sequence) tt");
        // return
        // super.countByQueryWithVaParam("select count(1) from MemberPackageInfo a where a.activeDealerId = ?1 and a.activeMemberId= ?2 and a.activeCarId = ?3",dealerId,memberId,carId)
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Oct 16, 2015
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#getMaxMemberPackageInfoByCardPrefix(java.lang.String)
     **/
    @Override
    public MemberPackageInfo getMaxMemberPackageInfoByNonPhysicalCardPrefix(String perfix) throws AppException {
        StringBuilder hql = new StringBuilder(
            "select * from tm_member_package_info a where a.package_number like '" + perfix
                + "%' order by a.package_number desc limit 0,1");
        List<MemberPackageInfo> list = super.getJpaTemplate().executeFind(
            new NativeQueryJpaCallback(hql.toString(), MemberPackageInfo.class));
        if (list != null && !list.isEmpty()) {
            return list.get(0);
        } else {
            return null;
        }
    }
    
    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Oct 16, 2015
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#getMaxMemberPackageInfoByCardPrefix(java.lang.String)
     **/
    @Override
    public MemberPackageInfo getMaxMemberPackageInfoByPhysicalCardPrefix(String perfix) throws AppException {
        StringBuilder hql = new StringBuilder(
            "select * from tm_member_package_info a where a.package_number like '" + perfix
                + "%' order by a.package_number desc limit 0,1");
        List<MemberPackageInfo> list = super.getJpaTemplate().executeFind(
            new NativeQueryJpaCallback(hql.toString(), MemberPackageInfo.class));
        if (list != null && !list.isEmpty()) {
            return list.get(0);
        } else {
            return null;
        }
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : 2015年10月22日
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#getDealerInfoDtoByDealerId(java.lang.Long)
     **/
    @Override
    public List<MemberAndDealerInfoDto> getMemberAndDealerInfoDtoByMemberId(Long id)
        throws AppException {
        String hql = "select a.DEALER_NAME, a.ADDRESS, b.MEMBER_NAME, b.MOBILE_PHONE"
            + " from tm_raw_dealer a, tm_raw_member b" + " where b.ID=" + id
            + " and a.id = b.DEALER_ID";
        List<Object[]> resultList = super.getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(hql));
        List<MemberAndDealerInfoDto> result = new ArrayList<MemberAndDealerInfoDto>();
        for (Object[] i : resultList) {
            MemberAndDealerInfoDto it = new MemberAndDealerInfoDto();
            it.setDealerName(Utils.getString(i[0]));
            it.setAddress(Utils.getString(i[1]));
            it.setMemberName(Utils.getString(i[2]));
            it.setMobilePhone(Utils.getString(i[3]));
            result.add(it);
        }
        return result;
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Oct 28, 2015
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#getCancelMemberPackageInfoMars(com.cap.bts.crmdp.dealer.purchase.domain.MemberPackageInfo)
     **/
    @Override
    public MemberPackageInfo getCancelMemberPackageInfoMars(MemberPackageInfo mpi)
        throws AppException {
        MemberPackageInfo memberPackageInfo=null;
        List<MemberPackageInfo> list = super
            .getListByQuery("from MemberPackageInfo a where a.packageNumber='"
                + mpi.getPackageNumber() + "'");
        if (list != null && !list.isEmpty()) {
            memberPackageInfo = list.get(0);
            }
        return memberPackageInfo;
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : 2015年10月20日
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#getDealerInfoDtoByDealerId(java.lang.Long)
     **/
    @Override
    public List<MemberAndDealerInfoDto> getMemberAndDealerInfoDtoByMARSIdAndDearId(Long dealerId,
        String marsMemberId) throws AppException {
        String hql = "select a.DEALER_NAME, a.ADDRESS, b.MEMBER_NAME, b.MOBILE_PHONE, b.ID"
            + " from tm_raw_dealer a, tm_raw_member b" + " where b.DEALER_ID =  " + dealerId
            + " and b.MARS_MEMBER_ID = '" + marsMemberId + "' and a.id = b.DEALER_ID";
        List<Object[]> resultList = super.getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(hql));
        List<MemberAndDealerInfoDto> result = new ArrayList<MemberAndDealerInfoDto>();
        for (Object[] i : resultList) {
            MemberAndDealerInfoDto it = new MemberAndDealerInfoDto();
            it.setDealerName(Utils.getString(i[0]));
            it.setAddress(Utils.getString(i[1]));
            it.setMemberName(Utils.getString(i[2]));
            it.setMobilePhone(Utils.getString(i[3]));
            it.setId(((BigInteger) i[4]).longValue());
            result.add(it);
        }
        return result;
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : 2015年10月29日
     * @author annson
     **/
    @Override
    public List<Object[]> getPackageListForReportByMember(Long memberId, Long carId)
        throws AppException {
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("  SELECT " + "   b.PRICE, " + "   b.NAME, " + "   a.PACKAGE_NUMBER, "
            + "   a.STATUS, " + "   a.PURCHASE_DATE, " + "   a.ACTIVE_DATE, "
            + "   a.EXPIRE_DATE, " + "   a.ID, " + "   a.ACTIVE_CAR_ID, " + "   c.car_plate, "
            + " b.MEMBER_BENFIT"
            + "  FROM " + "   TM_MEMBER_PACKAGE_INFO a, " + "   TM_CRM_PACKAGE_DEFINITION b, "
            + "   tm_raw_car_plate c " + "  WHERE " + "   a.ACTIVE_MEMBER_ID=  " + memberId
            + "   AND a.STATUS != " + PurchaseConstants.PACKAGE_STATUS_SALED
            + " and a.MARS_LOCK_STATUS = '"
            + PurchaseConstants.MARS_NO_LOCK_STATUS + "'"
            + "   AND a.PACKAGE_DEF_ID = b.ID  " + "   AND a.ACTIVE_CAR_ID = c.id ");
        if (carId != null) {
            sqlBuilder.append(" and a.ACTIVE_CAR_ID = ").append(carId);
        } else {
            sqlBuilder.append(" UNION SELECT " + "  b.PRICE, " + "  b.NAME, "
                + "  a.PACKAGE_NUMBER, " + "  a.STATUS, " + "  a.PURCHASE_DATE, "
                + "  a.ACTIVE_DATE, " + "  a.EXPIRE_DATE, " + "  a.ID, "
                + "  a.ACTIVE_CAR_ID, NULL, b.MEMBER_BENFIT " + " FROM " + "  TM_MEMBER_PACKAGE_INFO a, "
                + "  TM_CRM_PACKAGE_DEFINITION b " + " WHERE " + "  a.MEMBER_ID= " + memberId
                + "  AND a.STATUS = " + PurchaseConstants.PACKAGE_STATUS_SALED
                + " and a.MARS_LOCK_STATUS = '"
                + PurchaseConstants.MARS_NO_LOCK_STATUS + "'"
                + "  AND a.PACKAGE_DEF_ID = b.ID");

        }
        return super.getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback(sqlBuilder.toString()));
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Nov 4, 2015
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#getMemeberPackageInfoByPackageNumber(java.lang.String)
     **/
    @Override
    public MemberPackageInfo getMemeberPackageInfoByPackageNumber(String packageNumber)
        throws AppException {
        MemberPackageInfo mpi = null;
        List<MemberPackageInfo> list = super
            .getListByQuery("from MemberPackageInfo a where a.packageNumber='"
                + packageNumber+ "'");
        if (list != null && !list.isEmpty()) {
            mpi = list.get(0);
        }
        return mpi;

    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年11月18日
     * @see com.cap.bts.crmdp.dealer.purchase.dao.IMemberPackageDao#getPackagesPurchaseNumWithSamePackageCode(java.lang.Long, java.lang.Long, java.lang.Long, java.lang.String)
    **/
    @Override
    public int getPackagesPurchaseNumWithSamePackageCode(Long dealerId, Long memberId, Long carId,
        String code) throws AppException {
        return super
            .countByNativeQuery("select count(1) from TM_MEMBER_PACKAGE_INFO aa ,TM_CRM_PACKAGE_DEFINITION bb where aa.PACKAGE_DEF_ID=bb.id and aa.active_Dealer_Id = "
                + dealerId
                + " and aa.active_Member_Id= "
                + memberId
                + " and aa.active_Car_Id = "
                + carId
                + " and bb.code = '"
                + code + "'");
                //+ " and (aa.IS_GIFT is null or aa.IS_GIFT = 0)")
    }

	@Override
	public List<String> getfileDataList() throws AppException {
		String sql = "SELECT DISTINCT CONCAT(IFNULL(company,''),'±',IFNULL(mars_tenant_id,''),'±',IFNULL(CONCAT(mars_tenant_id,'_',DATE_FORMAT(NOW(),'%Y%m%d'),'.csv'),''))"
				+ " FROM tm_dealer_rel_mars"
				+ " WHERE dealer_id"
				+ " IN (SELECT dealer_num FROM vt_member_package_expiration"
				+ " WHERE DATE_FORMAT(expire_date,'%Y%m%d') = DATE_FORMAT(date_sub(curdate(),interval 1 day),'%Y%m%d'))"
				+ " and dealer_id IN (select cs.dealer_id from tm_mars_cutover_status cs WHERE cs.change_date < NOW())";
		return super.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<String>(sql));
	}

	@Override
	public List<Object[]> getExpireCompanyInfoFromDealerRelMars() throws AppException {
		String sql = "SELECT DISTINCT company,mars_tenant_id"
				+ " FROM tm_dealer_rel_mars"
				+ " WHERE dealer_id"
				+ " IN (SELECT dealer_num FROM vt_member_package_expiration"
				+ " WHERE DATE_FORMAT(expire_date,'%Y%m%d') = DATE_FORMAT(date_sub(curdate(),interval 1 day),'%Y%m%d'))"
				+ " and dealer_id IN (select cs.dealer_id from tm_mars_cutover_status cs WHERE cs.change_date < NOW())";
		return super.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<Object[]>(sql));
	}

	@Override
	public List<String> getExpirePackagefileDataList(String company) throws AppException {
		String sql ="SELECT CONCAT(IFNULL(mars_member_id,''),'±',IFNULL(mars_car_id,''),'±',package_number,'±',expire_date,'±',balance,'±',dealer_num,'±',IFNULL(so_number_sale,''))"
				+ " FROM ("
				+ " SELECT *"
				+ " FROM vt_member_package_expiration"
				+ " WHERE DATE_FORMAT(expire_date,'%Y%m%d') = DATE_FORMAT(date_sub(curdate(),interval 1 day),'%Y%m%d')"
				+ " ) e where dealer_num IN"
				+ " (SELECT dealer_id FROM tm_dealer_rel_mars"
				+ " WHERE DATE_FORMAT(expire_date,'%Y%m%d') = DATE_FORMAT(date_sub(curdate(),interval 1 day),'%Y%m%d')"
				+ " and company = '"+ company +"')"
				+ " and dealer_num IN (select cs.dealer_id from tm_mars_cutover_status cs WHERE cs.change_date < NOW())";
		return super.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<String>(sql));
	}

	@Override
	public List<Long> getcardDefIdByPackageDefId(Long packageDefId) throws AppException {
		String sql = "select a.CARD_DEF_ID from tm_crm_package_definition a where a.ID = " + packageDefId;
		return this.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<Long>(sql));
	}

	@Override
	public List<Object[]> getActiveExpireMemberPackageList() throws AppException {
		try {
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        	format.setTimeZone(TimeZone.getTimeZone("GMT+8:00"));
        	
			String sql = "SELECT " +
	                " a.ID, " +
	                " a.PURCHASE_DATE, " +
	                " a.EXPIRE_DATE, " +
	                " a.`STATUS`, " +
	                " a.DEALER_ID, " +
	                " a.ACTIVE_DEALER_ID, " +
	                " b.NAME, " +
	                " c.MEMBER_NAME, " +
	                " c.MOBILE_PHONE, " +
	                " a.PACKAGE_NUMBER, " +
	                " d.CAR_PLATE " +
	                " FROM " +
	                " tm_member_package_info a " +
	                " LEFT JOIN tm_crm_card_definition b ON b.ID = a.CARD_DEF_ID " +
	                " LEFT JOIN TM_RAW_MEMBER c ON c.ID = a.ACTIVE_MEMBER_ID " +
	                " LEFT JOIN tm_raw_car_plate d ON d.ID = a.ACTIVE_CAR_ID " +
	                " WHERE " +
	                " a. STATUS = " + PurchaseConstants.PACKAGE_STATUS_ACTIVED +
	                " AND datediff( " +
	                " a.EXPIRE_DATE, " +
	                " str_to_date('"+ format.format(new Date())+"', '%Y-%m-%d') " +
	                ") <= (select max(convert(CODE_VALUE, unsigned) ) from tc_code_value where CODE_TYPE = 'packageToBeExpiredDays') ";
            return getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<Object[]>(sql));
        } catch (Exception e) {
            throw new AppException("Exception getActiveMemberPackageList", e);
        }
	}

	@Override
	public void updatePackageStatusById(String idList, int status) throws AppException {
		String sql = "update tm_member_package_info set STATUS = '" + status + "'"
				+ ", LAST_UPDATE_DATE = now()"
				+ " where id in("+ idList +")";
		this.getEntityManager().createNativeQuery(sql).executeUpdate();
	}

	@Override
	public void updatePackageStatusById(Long id) throws AppException {
		String sql = "update tm_member_package_info a"
				+ " set STATUS = 3, LAST_UPDATE_DATE = now()"
				+ " where a.ID = ( SELECT b.PACKAGE_ID FROM  tm_member_package_redemption  b where  b.id  = "+ id +")";
		this.getEntityManager().createNativeQuery(sql).executeUpdate();
	}
	
	@Override
	public List<Object[]> getPackageStatusByMemberId(Long memberId) throws AppException{
		String sql = "select sum(case when IS_GIFT = 0 then 1 end) no_GIFT,sum(case when IS_GIFT = 1 then 1 end) IS_GIFT from tm_member_package_info where ACTIVE_MEMBER_ID =  " + memberId;
		return getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<Object[]>(sql));
	}
	
	@Override
	public void deleteGiftByMemberId(Long memberId) throws AppException{
		String sql = " update tm_member_package_info set ACTIVE_MEMBER_ID = null, ACTIVE_CAR_ID = null, ACTIVE_DEALER_ID = null,  MEMBER_ID = null, PURCHASE_DATE = null, EXPIRE_DATE= null , STATUS = 0 where  IS_GIFT = 1 and ACTIVE_MEMBER_ID =  " + memberId;
		
		this.getEntityManager().createNativeQuery(sql).executeUpdate();
	}

}
